from sqlalchemy import text, create_engine

import config
from app import my_app

from exts import db
from models import Departments, Employees, DeptEmp, DeptManager, DeptManagerTitle, Titles  # noqa

engine = create_engine(config.BaseConfig.DATABASE_URI)

insert_dept_manager_title_trigger = text("""
CREATE TRIGGER insert_dept_manager_title_trigger
AFTER INSERT
ON dept_manager FOR EACH ROW
INSERT INTO dept_manager_title (emp_no, from_date, to_date)
VALUES (NEW.emp_no, NEW.from_date, NEW.to_date)
""")

delete_dept_manager_title_trigger = text("""
CREATE TRIGGER delete_dept_manager_title_trigger
AFTER DELETE
ON dept_manager FOR EACH ROW
DELETE FROM dept_manager_title WHERE emp_no = OLD.emp_no
""")


def create_trigger():
    with engine.connect() as conn:
        conn.execute(insert_dept_manager_title_trigger)
        conn.execute(delete_dept_manager_title_trigger)


def create_table_and_trigger():
    with my_app.app_context():
        db.create_all()
        create_trigger()
